From 7844727a0be98d3df2dfa7075585438f3f2672cd Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 5 Aug 2026 21:26:28 +0000 Subject: [PATCH] Implement build_sightings() --- db/schemas/sokwedb/functions/Makefile | 4 +- .../functions/create/build_sightings.m4 | 271 ++++++++++++++++++ .../sokwedb/functions/drop/build_sightings.m4 | 24 ++ 3 files changed, 298 insertions(+), 1 deletion(-) create mode 100644 db/schemas/sokwedb/functions/create/build_sightings.m4 create mode 100644 db/schemas/sokwedb/functions/drop/build_sightings.m4 diff --git a/db/schemas/sokwedb/functions/Makefile b/db/schemas/sokwedb/functions/Makefile index 9351aa7..9756c55 100644 --- a/db/schemas/sokwedb/functions/Makefile +++ b/db/schemas/sokwedb/functions/Makefile @@ -18,7 +18,9 @@ # Karl O. Pinc # This determines the order in which the functions are put into the database. -ORDER := build_arrivals_seq build_swelling_states julian julian_to +ORDER := build_arrivals_seq build_swelling_states julian julian_to \ + build_sightings + ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/functions/create/build_sightings.m4 b/db/schemas/sokwedb/functions/create/build_sightings.m4 new file mode 100644 index 0000000..89955da --- /dev/null +++ b/db/schemas/sokwedb/functions/create/build_sightings.m4 @@ -0,0 +1,271 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Rebuild ARRIVALS.Seq functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') +include(`constants.m4') +include(`macros.m4') +include(`functions.m4') +dnl + +CREATE OR REPLACE FUNCTION build_sightings() + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY DEFINER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute all SIGHTINGS rows. + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_sightings() + -- + -- Returns: + -- The number of rows processed. + -- + -- Remarks: + + DECLARE + a_animid biography_data.animid%TYPE; + cnt INT := 0; + a_result INT := 0; + + BEGIN + FOR a_animid IN + SELECT biography_data.animid + FROM biography_data + ORDER BY biography_data.animid + LOOP + SELECT build_sightings(a_animid) + INTO a_result; + cnt := cnt + a_result; + END LOOP; + + RETURN cnt; + END; +$$; + + +CREATE OR REPLACE FUNCTION build_sightings(a_animid TEXT) + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY INVOKER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute the SIGHTINGS rows of an AnimId + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_sightings(animid) + -- + -- Input: + -- animid The BIOGRAPHY_DATA.AnimID of the individual who's + -- SIGHTINGS rows are to be be re-built. + -- + -- Returns: + -- The number of rows processed. + -- + -- Remarks: + -- Likely not as efficient as it might be. We don't care. + + DECLARE + a_result INT := 0; + + a_bdmin biography_data.bdmin%TYPE; + a_departdate biography_data.departdate%TYPE; + + a_date sightings.date%TYPE; + a_commid sightings.commid%TYPE; + + a_eid events.eid%TYPE; + + a_step sighting_controls.step%TYPE; + a_source sighting_controls.source%TYPE; + a_sightingrecord sighting_controls.sightingrecord%TYPE; + a_wtype sighting_controls.wtype%TYPE; + a_behavior sighting_controls.behavior%TYPE; + a_certainty sighting_controls.certainty%TYPE; + a_role sighting_controls.role%TYPE; + a_startsource sighting_controls.startsource%TYPE; + a_endsource sighting_controls.endsource%TYPE; + + do_insert BOOLEAN; + + BEGIN + -- Get the time period we'll be iterating over + SELECT biography_data.bdmin, biography_data.departdate + INTO a_bdmin , a_departdate + FROM biography_data + WHERE biography_data.animid = a_animid; + + IF NOT FOUND THEN + RETURN a_result; + END IF; + + -- Delete all the old data + DELETE FROM sightings + WHERE sightings.animid = a_animid; + + -- Go through every date the individual has a swelling source + a_date := a_bdmin; + LOOP + do_insert := false; + + -- Go through the SIGHTING_CONTROLS steps + FOR a_step, a_source + , a_sightingrecord + , a_wtype, a_behavior, a_certainty, a_role + , a_startsource, a_endsource + IN SELECT step , source + , sightingrecord + , wtype , behavior , certainty , role + , startsource , endsource + FROM sighting_controls + ORDER BY step + LOOP + CASE a_source + + WHEN 'sdb_roles_sc' THEN + -- Look at EVENTS, etc. + SELECT obs.eid, obs.commid + INTO a_eid , a_commid + FROM obs + JOIN roles ON (roles.eid = obs.eid) + WHERE obs.date = a_date + AND obs.animid = a_animid + AND obs.type = a_wtype + AND obs.behavior = a_behavior + AND obs.certainty = a_certainty + AND roles.role = a_role; + + IF FOUND THEN + -- Determine the community + CASE a_behavior + WHEN 'sdb_utm' THEN + SELECT locations_utm.commid + INTO STRICT a_commid + FROM locations_utm + WHERE locations_utm.eid = a_eid; + WHEN 'sdb_paper' THEN + SELECT locations_paper.commid + INTO STRICT a_commid + FROM locations_paper + WHERE locations_paper.eid = a_eid; + WHEN 'sdb_aggression' THEN + SELECT aggressions.commid + INTO STRICT a_commid + FROM aggressions + WHERE aggressions.eid = a_eid; + WHEN 'sdb_mating' THEN + SELECT matings.commid + INTO STRICT a_commid + FROM matings + WHERE matings.eid = a_eid; + WHEN 'sdb_pg_event' THEN + SELECT pantgrunts.commid + INTO STRICT a_commid + FROM pantgrunts + WHERE pantgrunts.eid = a_eid; + ELSE NULL; -- Use obs.commid + END CASE; + + do_insert := true; + EXIT; + END IF; + + WHEN 'sdb_comm_membs_sc' THEN + SELECT comm_membs.commid + INTO a_commid + FROM comm_membs + WHERE comm_membs.animid = a_animid + AND comm_membs.startdate <= a_date + AND a_date <= comm_membs.enddate + AND comm_membs.startsource = a_startsource + AND comm_membs.endsource = a_endsource; + + IF FOUND THEN + do_insert := true; + EXIT; + END IF; + + WHEN 'sdb_swelling_sources_sc' THEN + PERFORM 1 + FROM swelling_sources + WHERE swelling_sources.animid = a_animid + AND swelling_sources.date = a_date + AND swelling_sources.source = a_sightingrecord; + + IF FOUND THEN + -- Find the CommID to use + SELECT comm_membs.commid + INTO a_commid + FROM comm_membs + WHERE comm_membs.animid = a_animid + AND comm_membs.startdate <= a_date + AND a_date <= comm_membs.enddate; + + IF NOT FOUND THEN + a_commid := 'sdb_unknown_comm'; + END IF; + + do_insert := true; + EXIT; + END IF; + + WHEN 'sdb_non_brec_sighting_sources_sc' THEN + SELECT non_brec_sighting_sources.commid + INTO a_commid + FROM non_brec_sighting_sources + WHERE non_brec_sighting_sources.animid = a_animid + AND non_brec_sighting_sources.date = a_date + AND non_brec_sighting_sources.source = a_sightingrecord; + + IF FOUND THEN + do_insert := true; + EXIT; + END IF; + + END CASE; + END LOOP; + + IF do_insert THEN + INSERT INTO sightings (date, animid, commid, step) + VALUES (a_date, a_animid, a_commid, a_step); + + a_result := a_result + 1; + END IF; + + a_date := a_date + 1; + + EXIT WHEN a_date > a_departdate; + END LOOP; + + RETURN a_result; + END; +$$; + + +grant_everybody_func_priv(`build_sightings()') +grant_everybody_func_priv(`build_sightings(TEXT)') diff --git a/db/schemas/sokwedb/functions/drop/build_sightings.m4 b/db/schemas/sokwedb/functions/drop/build_sightings.m4 new file mode 100644 index 0000000..d6ab68f --- /dev/null +++ b/db/schemas/sokwedb/functions/drop/build_sightings.m4 @@ -0,0 +1,24 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop ARRIVALS.Seq rebuilding functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') + +DROP FUNCTION IF EXISTS build_sightings(); +DROP FUNCTION IF EXISTS build_sightings(TEXT); -- 2.34.1